package twitter;

import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.net.Socket;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Hashtable;
import java.util.Vector;

public class ClientHandler extends Thread{

	Socket socket;
	Connection myConnection;
    Statement statement = null;
    ResultSet rs = null;
    ServerView view;
    
    private final String followers = "followers";
    private final String followings = "followings";
    private final int UPDATING = 1;
    private final int NOTUPDATING = 0;
    
    static Object lock = new Object();
    
    Hashtable<Integer, Integer> userTable = TwitterServer.userTable;
    Vector<ClientHandler> cHandlersList = TwitterServer.cHandlersList;
    static Vector<User> sentUsers = new Vector<User>();
    
    
	public ClientHandler(Connection myConnection, Socket socket, ServerView view){
		this.view = view;
		this.myConnection = myConnection;
		this.socket = socket;
	}
	
	@Override
	public synchronized void run() {
		try {
			while(true){
				ObjectInputStream ois = new ObjectInputStream(socket.getInputStream());
				// Opening the package from the client
				Command command = (Command) ois.readObject();
				User user = (User) command.data;
				switch(command.commandId){
					// case 0: getting a user to update from the db and send it to client
					case 0:
						Command pack;
						user = getNextUser();
						if(user == null){
							pack = new Command(null, 2);
						}else{
							pack = new Command(user,0);
						}
						//System.out.println("Delivering userId: " +user.id);
						view.infoLabel.setText("Delivering userId: " +user.id);
						
						ObjectOutputStream oos = new ObjectOutputStream(socket.getOutputStream());
						oos.writeObject(pack);
						oos.close();
						break;
					// case 1: getting a user info from client and put it into the db
					case 1:
						// delete the user from the sentUsers list
						synchronized (sentUsers) {
							for(User u:sentUsers){
								if(u.id.equals(user.id)){
									sentUsers.remove(u);
									break;
								}
							}
						}
						if(user.screenName != null){
							UpdateUserInfo(user);
						}
						
						checkAndInsertUsers(user.followers, user.id, followers);
						checkAndInsertUsers(user.following, user.id, followings);
											
						insertTweet(user.id, user.tweets);
						
						setLastUpdate(user.id);
						setUpdating(user.id, NOTUPDATING);
						
						view.infoLabel.setText("finished updating user: " + user.id);
						TwitterServer.numUpdatedUser++;
						view.userLabel.setText("Number of updated users: "+ TwitterServer.numUpdatedUser);
						break;
					// case 2: user was not updated. setting user status to 0
					case 2:
						synchronized (sentUsers) {
							for(User u:sentUsers){
								if(u.id.equals(user.id)){
									sentUsers.remove(u);
									setUpdating(u.id, NOTUPDATING);
									break;
								}
							}
						}
						view.infoLabel.setText("user: " + user.id + " returned without update");
						//System.err.println("user: " + user.id + " returned without update");
						break;
					// client send im alive messages
					case 3:
						long actualTime = getDate().getTime();
						synchronized (sentUsers) {
							for(User u:sentUsers){
								if(u.id.equals(user.id)){
									u.checkTime = actualTime;
									break;
								}else if((actualTime-u.checkTime) > 60000){
									view.infoLabel.setText(u.id +": client is dead, setting user back to 0");
									//System.err.println(u.id +": client is dead, setting user back to 0");
									sentUsers.remove(u);
									setUpdating(u.id, 0);
									break;
								}
							}
						}
						break;
					// update an ssked user
					case 4:
						Command pack1;
						User u = getUserInfoFromDB(user);
						if(u == null){
							pack1 = command;
						}else{
							if(u.updating==1){
								pack1 = new Command(null, 4);
							}else{
								u.lastTweet = getlastTweetId(u.id);
								setUpdating(u.id, UPDATING);
								u.checkTime = getDate().getTime();
								synchronized (sentUsers) {
							    	sentUsers.add(u);
								}
								pack1 = new Command(u, 4);
							}
						}
						view.infoLabel.setText("deliver user request");
					    ObjectOutputStream os = new ObjectOutputStream(socket.getOutputStream());
						os.writeObject(pack1);
						os.close();
						break;
				}
				ois.close();
				socket.close();
				try {
					synchronized (cHandlersList) {
						cHandlersList.add(this);
					}
					System.out.println(cHandlersList.size()+"/"+TwitterServer.threadNum+" Threads are waiting in the list.");
					wait();
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			TwitterServer.threadNum--;
		}
	}
	/**
	 * update the user information in the DB
	 * @param user
	 * @throws SQLException
	 */
	private void UpdateUserInfo(User user) throws SQLException {
		statement = myConnection.createStatement();
		if(user.name != null)user.name = this.deleteApostrophe(user.name);
		if(user.screenName != null)user.screenName = this.deleteApostrophe(user.screenName);
		if(user.description!= null)user.description = this.deleteApostrophe(user.description);
		if(user.location!= null)user.location = this.deleteApostrophe(user.location);
		if(user.url!= null)user.url = this.deleteApostrophe(user.url);
		
		if(getUserInfoFromDB(user)==null){
			Integer key = new Integer(Integer.valueOf(user.id));
			if(!userTable.containsKey(key)){
				userTable.put(key, key);
			}
			setUpdating(user.id, 1);
			user.updating = 1;
			user.last_update = getDate();
			statement.executeUpdate("INSERT INTO user VALUES('"+user.id+"'," +
					" '"+user.name+"','"+user.screenName+"','"+user.location+"'," +
							"'"+user.description+"','"+user.url+"','"+user.last_update+"','"+user.updating+"','"+user.language+"')");
		}else{
			statement.executeUpdate("UPDATE user " +
					"SET name='"+user.name+"', screen_name='"+user.screenName+"', location='"+user.location+"', description='"+user.description+"', url='"+user.url+"', language='"+user.language+"'" +
					"WHERE id='"+user.id+"'");
		}
		
	}
	/**
	 * get the next user from the DB who was first updated, along with the time of the last tweet of him.
	 * @return
	 * @throws SQLException
	 */
	public User getNextUser() throws SQLException {
		String userId;
		User user;
		synchronized (lock) {
			statement = myConnection.createStatement();
			rs = statement.executeQuery("SELECT id, last_update FROM user WHERE updating = 0 ORDER BY last_update ASC, id LIMIT 1");
			
			if(!rs.next()){
				return null;
			}
			userId = rs.getString(1);
			
			rs = statement.executeQuery("SELECT name,screen_name,location,description,url,last_update FROM user where id = " + userId);
			rs.next();
			user = new User(userId,rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),null,null);
			setUpdating(userId, UPDATING);
		}

	    user.lastTweet = getlastTweetId(userId);
	    user.checkTime = getDate().getTime();
	    synchronized (sentUsers) {
	    	sentUsers.add(user);
		}
	    rs.close();
		return user;
	}
	/**
	 * insert the new tweets from a user to the tweets table in the DB
	 * @param userId
	 * @param tweets
	 * @param step is the number of tweets for each insert
	 */
	public void insertTweet(String userId, Tweet[] tweets){
		if(tweets == null || tweets.length == 0)return;
		
		int limit = 100;
		boolean stop = true;
		for(int i = 0; stop; i = i+limit){
			String query = "INSERT INTO tweets VALUES ";
			for(int j = 0; j < limit;j++){
				if(i+j >= tweets.length){
					stop = false;
					break;
				}
				Tweet tweet = tweets[i+j];
				Timestamp published = Timestamp.valueOf(tweet.published);
				if(tweet.content!= null) tweet.content = this.deleteApostrophe(tweet.content);
				if(tweet.source!= null) tweet.source = this.deleteApostrophe(tweet.source);
				if(tweet.place!= null) tweet.place = this.deleteApostrophe(tweet.place);
				query = query + "('"+tweet.id+"', '"+userId+"', '"+tweet.content+"', '"+published+"'," +
								"'"+getDate()+"', '"+tweet.source+"', '"+tweet.place+"'),";
			}
			try{
				statement = myConnection.createStatement();
				query = query.substring(0, query.length()-1);
		    	statement.executeUpdate(query);
				}
				catch (SQLException e) {
					System.err.println("ERROR in inserting tweets for user: " + userId);
					e.printStackTrace();
				}catch(IllegalArgumentException e){
					e.printStackTrace();
				}
		}
	}
	/**
	 * add the new users from the user's following,followers lists to the vector list.
	 * also update the following, followers lists of the user.
	 * @param listOfUsers
	 * @param id
	 * @param table
	 */
	public void checkAndInsertUsers(String listOfUsers, String id, String table){
		
		if(listOfUsers == null || listOfUsers.length() < 3) return; 
		
		listOfUsers = listOfUsers.substring(0, listOfUsers.length()-1);
		String[] users = listOfUsers.split(",");
		
		Timestamp ts = Timestamp.valueOf("2011-01-01 00:00:00");
		String query = "INSERT INTO user VALUES ";
		
		//checking for new users
		for(String user:users) {
			Integer key = new Integer(Integer.valueOf(user));
			if(!userTable.containsKey(key)){
				userTable.put(key, key);
				query = query + "('"+user+"', null, null, null, null, null , '"+ts+"' , '"+NOTUPDATING+"',null),";
		   	}
		}
		
		try {
			statement = myConnection.createStatement();
			//inserting the new users to the DB
			query = query.substring(0, query.length()-1);
			if(!query.endsWith("S")){
				statement.executeUpdate(query);
			}
			//updating the following/followers of the user
			rs = statement.executeQuery("SELECT id FROM " + table + " WHERE id = " +id );
			if(!rs.next()){
				statement.executeUpdate("INSERT INTO " +table+ " VALUES ('"+id+"', '"+listOfUsers+"')");
			}else{
				statement.executeUpdate("UPDATE " +table+ " SET " +table+ " = '"+listOfUsers+"' WHERE id = " + id);
			}
		} catch (SQLException e) {
			System.err.println("ERROR:  " + query);
			e.printStackTrace();
		}
	}
	/**
	 * get the current system date and time
	 * @return the current time as a TimeStamp object
	 */
	public Timestamp getDate(){
		java.util.Date today = new java.util.Date();
		return new java.sql.Timestamp(today.getTime());
	}
	/**
	 * set the updating status of the user. 0 for available and 1 for busy.
	 * @param id
	 * @param num
	 * @throws SQLException
	 */
	private void setUpdating(String id, int num) throws SQLException {
		statement = myConnection.createStatement();
		statement.executeUpdate("UPDATE user SET updating = '"+num+"' WHERE id =" + id);
	}
	/**
	 * set the time that the user was last updated
	 * @param id
	 * @throws SQLException
	 */
	private void setLastUpdate(String id) throws SQLException {
		Timestamp lastUpdate = this.getDate();
		statement = myConnection.createStatement();
		statement.executeUpdate("UPDATE user SET last_update =  '"+lastUpdate+"'  WHERE id = '"+id+"'");
	}
	/**
	 * removing the Apostrophes from a specific string
	 * @param string
	 * @return
	 */
	public String deleteApostrophe(String string){
		string = string.replaceAll("'", "");
		string = string.replaceAll("\\\\" , "\\\\\\\\");
		return string;
	}
	/**
	 * set the thread socket and wakening up the thread if he is sleeping
	 * @param socket
	 */
	public synchronized void setSocket(Socket socket){
		this.socket = socket;
		notify();
	}
	/**
	 * return the id of the last tweet from the given user
	 * @param userId
	 * @return
	 */
	public String getlastTweetId(String userId){
		try {
			statement = myConnection.createStatement();
			rs = statement.executeQuery("SELECT max(id) FROM tweets WHERE owner = " + userId);
			if(rs.next()){
				return rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * get the user id from the DB according to the screen name
	 * @param user
	 * @return
	 */
	public User getUserInfoFromDB(User user){
		User u;
		try {
			statement = myConnection.createStatement();
			if(user.id == null){
				rs = statement.executeQuery("SELECT id,updating FROM user WHERE screen_name = '"+user.screenName+"'");
			}else{
				rs = statement.executeQuery("SELECT id,updating FROM user WHERE id = '"+user.id+"'");
			}
			if(rs.next()){
				u = new User(rs.getString(1),null,user.screenName,null,null,null,null,null);
				u.updating = rs.getInt(2);
				return u;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	
}
